﻿using log4net;
using MySqlConnector;
using System.Data;
using System.Reflection;
using Koala.Pro.CodeGenerator.UI.DatabaseModel.Schema;

namespace Koala.Pro.CodeGenerator.UI.DatabaseModel.Data
{
    public class MySql : Database
    {
        private static readonly ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public override IDictionary<string, DatabaseInfo> ListDatabases()
        {
            IDictionary<string, DatabaseInfo> dictionary = new Dictionary<string, DatabaseInfo>();
            string cmdText = $"show databases; ";
            using MySqlConnection mySqlConnection = new MySqlConnection(base.ConnectString);
            mySqlConnection.Open();
            MySqlCommand mySqlCommand = new MySqlCommand(cmdText, mySqlConnection);
            IDataReader dataReader = mySqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                DatabaseInfo databaseInfo = new DatabaseInfo();
                databaseInfo.Name = new NameElement(dataReader["database"].ToString());
                dictionary.Add(databaseInfo.Name.Name.ToString(), databaseInfo);
            }
            dataReader.Close();
            return dictionary;
        }

        public override IDictionary<string, TableInfo> ListTables(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            IDictionary<string, TableInfo> dictionary = new Dictionary<string, TableInfo>();
            string text = $"show tables;";
            log.Debug("ListTables sql:" + text);
            using MySqlConnection mySqlConnection = new MySqlConnection(base.ConnectString);
            mySqlConnection.Open();
            mySqlConnection.ChangeDatabase(database);
            IDbCommand dbCommand = new MySqlCommand(text, mySqlConnection);
            IDataReader dataReader = dbCommand.ExecuteReader();
            while (dataReader.Read())
            {
                string text2 = dataReader[0].ToString();
                log.Debug("tablename:" + text2);
                TableInfo tableInfo = new TableInfo();
                tableInfo.Name = new NameElement(text2);
                dictionary.Add(tableInfo.Name.Name.ToString(), tableInfo);
            }
            dataReader.Close();
            return dictionary;
        }

        private DataTable GetReaderSchema(string database, string tableName, MySqlConnection conn)
        {
            IDbCommand dbCommand = new MySqlCommand();
            string text = $"select * from `{tableName}`;";
            log.Debug("GetReaderSchema sql:" + text);
            dbCommand.CommandText = text;
            dbCommand.Connection = conn;
            using IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
            return dataReader.GetSchemaTable();
        }

        public override IDictionary<string, ColumnInfo> ListColumns(string database, string tableName)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            if (tableName == null)
            {
                throw new ArgumentNullException("tableName");
            }
            IDictionary<string, ColumnInfo> dictionary = new Dictionary<string, ColumnInfo>();
            MySqlConnection mySqlConnection = new MySqlConnection(base.ConnectString);
            mySqlConnection.Open();
            mySqlConnection.ChangeDatabase(database);
            DataTable readerSchema = GetReaderSchema(database, tableName, mySqlConnection);
            foreach (DataRow row in readerSchema.Rows)
            {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.Name = new NameElement(row["ColumnName"].ToString());
                columnInfo.Ordinal = Convert.ToInt32(row["ColumnOrdinal"].ToString());
                columnInfo.AllowDBNull = (bool)row["AllowDBNull"];
                columnInfo.MaxLength = Convert.ToInt32(row["ColumnSize"].ToString());
                columnInfo.DataTypeId = Convert.ToInt32(row["ProviderType"].ToString());
                columnInfo.DataType = ((MySqlDbType)row["ProviderType"]).ToString();
                columnInfo.FieldPrecise = Convert.ToInt32(row["NumericPrecision"].ToString());
                columnInfo.AutoIncrement = (bool)row["IsAutoIncrement"];
                columnInfo.IsPrimaryKey = (bool)row["IsKey"];
                columnInfo.Unique = (bool)row["IsUnique"];
                columnInfo.IsReadOnly = (bool)row["IsReadOnly"];
                string name = row["DataType"].ToString();
                string simplyNetType = GetSimplyNetType(row["DataType"].ToString());
                columnInfo.NetType = new NameElement(name, simplyNetType);
                columnInfo.UIControlType = GetControlType(row["DataType"].ToString());
                dictionary.Add(columnInfo.Name.Name.ToString(), columnInfo);
            }
            SetDefaultValueAndPrimaryKey(dictionary, tableName, mySqlConnection);
            mySqlConnection.Close();
            return dictionary;
        }

        public override IDictionary<string, ViewInfo> ListViews(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            return new Dictionary<string, ViewInfo>();
        }

        public override IDictionary<string, ProcedureInfo> ListProcedures(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            return new Dictionary<string, ProcedureInfo>();
        }

        private void SetDefaultValueAndPrimaryKey(IDictionary<string, ColumnInfo> list, string tableName, MySqlConnection connection)
        {
            string text = $" DESC `{tableName}`;";
            log.Debug("Get DefaultValue sql:" + text);
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(text, connection);
            DataSet dataSet = new DataSet();
            mySqlDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            foreach (ColumnInfo value in list.Values)
            {
                try
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (value.Name.Name.ToString() == row["Field"].ToString())
                        {
                            string defaultValue = row["Default"].ToString();
                            value.DefaultValue = defaultValue;
                            if (row["Key"].ToString() == "PRI")
                            {
                                value.IsPrimaryKey = true;
                            }
                            log.Debug($"Column {value.Name.Name}'s defaultValue:{value.DefaultValue}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Debug(ex.Message);
                    log.Debug(ex.StackTrace);
                }
            }
        }

        public override DataSet ExecuteSql(string database, string query)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            DataSet dataSet = new DataSet();
            using MySqlConnection mySqlConnection = new MySqlConnection(base.ConnectString);
            mySqlConnection.Open();
            mySqlConnection.ChangeDatabase(database);
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, mySqlConnection);
            try
            {
                mySqlDataAdapter.Fill(dataSet, Guid.NewGuid().ToString());
                return dataSet;
            }
            finally
            {
                ((IDisposable)(object)mySqlDataAdapter)?.Dispose();
            }
        }

        public override bool TestConnection()
        {
            bool result = false;
            IDbConnection dbConnection = new MySqlConnection(base.ConnectString);
            try
            {
                if (dbConnection.State == ConnectionState.Closed)
                {
                    dbConnection.Open();
                }
                if (dbConnection.State == ConnectionState.Open)
                {
                    result = true;
                }
            }
            catch
            {
            }
            finally
            {
                dbConnection.Close();
            }
            return result;
        }
    }

}
